<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of RecordModel
 *
 * @author Marko
 */
class Record_model extends CI_Model {

    public function __construct() {
        parent::__construct();
        $this->load->model('Team_model', 'TeamModel');
        $this->load->model('Athlete_model', 'AthleteModel');
        $this->load->model('Event_model', 'EventModel');
    }

    public function fetch_recrods_for_tuk($pool, $gender, $low_age, $high_age) {

//        $query = $this->db->query("SELECT athletes.id as athlete_id, competitions.id as competition_id, `distance`,`stroke`, first_name, last_name, 
//            CASE WHEN `stroke`='free' THEN 0 WHEN `stroke`='back' THEN 1 WHEN `stroke`='breast' THEN 2 WHEN `stroke`='fly' THEN 3 ELSE 4 END as `order`, 
//            `pool`, `result`, `fina`, `date`, `city`, `country`, `name` 
//            FROM `results`, `competitions`, athletes 
//            WHERE UPPER(athletes.gender) = UPPER('$gender') AND athletes.id = athlete_id AND team = 'Tartu Ujumisklubi' AND results.competition_id=competitions.id AND `distance` >= 50 AND 
//            `results`.`result_ms` IN (SELECT min(`r`.`result_ms`) 
//            FROM `results` AS `r`, `competitions` AS `c`, athletes as a
//            WHERE c.pool=$pool AND UPPER(a.gender) = UPPER('$gender') AND results.stroke = r.stroke AND results.distance = r.distance AND
//            team = 'Tartu Ujumisklubi' AND a.id=r.athlete_id AND c.id = r.competition_id AND
//            YEAR(CURDATE()) - YEAR(a.dob) - (DATE_FORMAT(CURDATE(), '%m%d') < DATE_FORMAT(a.dob, '%m%d')) BETWEEN $low_age AND $high_age
//            ORDER BY `r`.`result_ms`) GROUP BY `distance`,`stroke` ORDER BY `order`, `distance`;");

        $query = $this->db->query("SELECT a.id as athlete_id, c.id as competition_id, last_name, first_name, main.distance, main.stroke, main.result, main.date, c.city, c.name, c.country FROM athletes as a, results as main , competitions as c ,(
        SELECT min(result_ms) as ms, r.distance, r.stroke, c.id FROM results as r, events as e, athletes as a, competitions as c 
        WHERE c.pool=$pool AND r.competition_id = c.id AND team = 'Tartu Ujumisklubi' AND r.distance>=50 AND e.id = r.event_id AND UPPER(e.gender) = UPPER('$gender') 
            AND YEAR(r.date) - YEAR(a.dob) - (DATE_FORMAT(r.date, '%m%d') < DATE_FORMAT(a.dob, '%m%d')) BETWEEN $low_age AND $high_age AND r.athlete_id = a.id
        GROUP BY r.stroke, r.distance) sub 
        WHERE
        YEAR(main.date) - YEAR(a.dob) - (DATE_FORMAT(main.date, '%m%d') < DATE_FORMAT(a.dob, '%m%d')) BETWEEN $low_age AND $high_age AND
        team = 'Tartu Ujumisklubi' AND main.result_ms = sub.ms AND main.competition_id = c.id AND main.stroke = sub.stroke AND main.distance = sub.distance AND main.athlete_id = a.id ORDER BY CASE WHEN main.stroke='free' THEN 0 WHEN main.stroke='back' THEN 1 WHEN main.stroke='breast' THEN 2 WHEN main.stroke='fly' THEN 3 ELSE 4 END, distance;");

//        $relays_query = $this->db->query("SELECT athlete_nr1_id, athlete_nr2_id, athlete_nr3_id, athlete_nr4_id, relay_id, competitions.id as competition_id, results.distance, results.stroke,
//            CASE WHEN results.stroke='free' THEN 0 WHEN results.stroke='back' THEN 1 WHEN results.stroke='breast' THEN 2 WHEN results.stroke='fly' THEN 3 ELSE 4 END as order_, 
//            pool, result, fina, results.`date`, city, country, name
//            FROM results, competitions, relays, events
//            WHERE results.event_id = events.id AND UPPER(events.gender) = UPPER('$gender') AND NOT relay_id = 0 AND results.relay_id = relays.id AND results.team = 'Tartu Ujumisklubi' AND results.competition_id=competitions.id AND results.distance >= 50 AND 
//            results.result_ms IN 
//			
//            (SELECT min(r.result_ms) 
//            FROM results AS r, competitions AS c, events as e
//            WHERE c.pool=$pool AND r.event_id = e.id AND UPPER(e.gender) = UPPER('$gender') AND r.team = 'Tartu Ujumisklubi' AND results.relay_id = r.relay_id AND c.id = r.competition_id AND results.stroke = r.stroke AND results.distance = r.distance AND
//            YEAR(r.date) - YEAR(
//            (SELECT min(dob) FROM athletes as a WHERE a.id = athlete_nr1_id OR a.id = athlete_nr2_id OR a.id = athlete_nr3_id OR a.id=athlete_nr4_id)
//            ) - (DATE_FORMAT(r.date, '%m%d') < DATE_FORMAT(
//            (SELECT min(dob) FROM athletes as a WHERE a.id = athlete_nr1_id OR a.id = athlete_nr2_id OR a.id = athlete_nr3_id OR a.id=athlete_nr4_id), '%m%d')
//            ) BETWEEN $low_age AND $high_age
//            AND
//            YEAR(r.date) - YEAR(
//            (SELECT max(dob) FROM athletes as a WHERE a.id = athlete_nr1_id OR a.id = athlete_nr2_id OR a.id = athlete_nr3_id OR a.id=athlete_nr4_id)
//            ) - (DATE_FORMAT(r.date, '%m%d') < DATE_FORMAT(
//            (SELECT max(dob) FROM athletes as a WHERE a.id = athlete_nr1_id OR a.id = athlete_nr2_id OR a.id = athlete_nr3_id OR a.id=athlete_nr4_id), '%m%d')
//            ) BETWEEN $low_age AND $high_age
//			ORDER BY r.result_ms) 
//			
//			GROUP BY results.distance, results.stroke 
//			ORDER BY order_, results.distance;");
        
        $relays_query = $this->db->query("SELECT athlete_nr1_id, athlete_nr2_id, athlete_nr3_id, athlete_nr4_id, main.relay_id, c.id as competition_id,  main.distance, main.stroke, main.result, main.date, c.city, c.name, c.country FROM relays, results as main , competitions as c, (
        SELECT min(result_ms) as ms, r.distance, r.stroke, c.id FROM results as r, events as e, competitions as c, relays WHERE c.pool=$pool AND r.competition_id = c.id AND r.team = 'Tartu Ujumisklubi' AND r.distance>=50 AND e.id = r.event_id AND UPPER(e.gender) = UPPER('$gender') AND NOT r.relay_id = 0 AND r.relay_id = relays.id
        AND
            YEAR(r.date) - YEAR(
            (SELECT min(dob) FROM athletes as a WHERE a.id = athlete_nr1_id OR a.id = athlete_nr2_id OR a.id = athlete_nr3_id OR a.id=athlete_nr4_id)
            ) - (DATE_FORMAT(r.date, '%m%d') < DATE_FORMAT(
            (SELECT min(dob) FROM athletes as a WHERE a.id = athlete_nr1_id OR a.id = athlete_nr2_id OR a.id = athlete_nr3_id OR a.id=athlete_nr4_id), '%m%d')
            ) BETWEEN $low_age AND $high_age
            AND
            YEAR(r.date) - YEAR(
            (SELECT max(dob) FROM athletes as a WHERE a.id = athlete_nr1_id OR a.id = athlete_nr2_id OR a.id = athlete_nr3_id OR a.id=athlete_nr4_id)
            ) - (DATE_FORMAT(r.date, '%m%d') < DATE_FORMAT(
            (SELECT max(dob) FROM athletes as a WHERE a.id = athlete_nr1_id OR a.id = athlete_nr2_id OR a.id = athlete_nr3_id OR a.id=athlete_nr4_id), '%m%d')
            ) BETWEEN $low_age AND $high_age
            GROUP BY r.stroke, r.distance
        
            ) r 
        WHERE main.team = 'Tartu Ujumisklubi' AND main.competition_id = c.id AND relays.id = main.relay_id AND main.result_ms = r.ms AND main.stroke = r.stroke AND main.distance = r.distance ORDER BY CASE WHEN main.`stroke`='free' THEN 0 WHEN main.`stroke`='back' THEN 1 WHEN main.`stroke`='breast' THEN 2 WHEN main.`stroke`='fly' THEN 3 ELSE 4 END, distance;");
        $relays = $relays_query->result();
        foreach ($relays as &$row) {
            for ($i = 1; $i <= 4; $i++) {
                if ($row->{"athlete_nr{$i}_id"} != 0) {
                    $row->relay[] = $this->db->select('id, first_name, last_name')->from('athletes')->where('id', $row->{"athlete_nr{$i}_id"})->get()->row();
                }
            }
        }

        return array_merge($query->result(), $relays);
    }

}

?>
